"""
使用数据库连接池管理数据库连接，提高数据库连接的复用率。
"""
# pip install dbUtils
from dbutils.pooled_db import PooledDB

from pymysql import cursors

# pip install pymysql
import pymysql



POOL = PooledDB(
    creator=pymysql,
    maxconnections=10,
    mincached=2,
    maxcached=3,
    blocking=True,
    setsession=[],
    ping=0,
    host='127.0.0.1', port=3306, user='root', passwd='root', charset='utf8', db='video'
)


def fetch_one(sql, params):
    #conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', charset='utf8', db='demo')
    conn = POOL.connection()
    cursor = conn.cursor()
    cursor.execute(sql, params)
    result = cursor.fetchone()
    cursor.close()
    conn.close() # 使用了连接池之后，此处的close方式是将链接放回到池中，不是断开连接。
    return result

def fetch_one_as_dict(sql, params):
    #conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', charset='utf8', db='demo')
    conn = POOL.connection()
    cursor = conn.cursor(cursor = cursors.DictCursor)
    cursor.execute(sql, params)
    result = cursor.fetchone()
    cursor.close()
    conn.close() # 使用了连接池之后，此处的close方式是将链接放回到池中，不是断开连接。
    return result

def fetch_all_as_dict(sql, params):
    #conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', charset='utf8', db='demo')
    conn = POOL.connection()
    cursor = conn.cursor(cursor = cursors.DictCursor)
    cursor.execute(sql, params)
    result = cursor.fetchall()
    cursor.close()
    conn.close() # 使用了连接池之后，此处的close方式是将链接放回到池中，不是断开连接。
    return result

def add_one(sql, params):
    conn = POOL.connection()
    cursor = conn.cursor()
    cursor.execute(sql, params)
    conn.commit()
    cursor.close()
    conn.close()
    return cursor.lastrowid

def delete_one(sql, params):
    conn = POOL.connection()
    cursor = conn.cursor()
    cursor.execute(sql, params)
    conn.commit()
    cursor.close()
    conn.close()
    return cursor.rowcount

def update_one(sql, params):
    conn = POOL.connection()
    cursor = conn.cursor()
    cursor.execute(sql, params)
    conn.commit()
    cursor.close()
    conn.close()
    return cursor.rowcount


def execute_update(update_sql, params):
    """
    执行任意 SQL 语句
    :param update_sql:
    :param params: 参数列表
    :return: 受影响的行数
    """
    conn = POOL.connection()
    cursor = conn.cursor()
    try:
        cursor.execute(update_sql, params)
        conn.commit()  # 提交事务
        return cursor.rowcount  # 返回受影响的行数
    except Exception as e:
        conn.rollback()  # 出现异常时回滚事务
        print(f"执行 SQL 时出错: {e}")
        raise
    finally:
        cursor.close()
        conn.close()  # 将连接放回连接池